﻿using Dapper;
using System.Collections.Generic;
using System.Linq;
using zhangmenren.Db;
using zhangmenren.Models;
using zhangmenren.Models.ViewModle;
using zhangmenren.ViewModel;

namespace zhangmenren.Repository
{
	public class OrderRepository : BaseRepository
	{
		public int Add(OrderEntity entity)
		{
			string sql = @"INSERT INTO [Order] (OrderId,PayType, UserId,WorkId,Price,WorkTitle,Num,State,AddDT) VALUES (@OrderId,@PayType, @UserId,@WorkId,@Price,@WorkTitle,@Num,0,GETDATE())";
			return Execute(conn => conn.Execute(sql, entity));
		}

		public int Payed(string orderId)
		{
			string sql = @"UPDATE [Order] SET State = 1,PayDT = GETDATE() WHERE OrderId = @OrderId";
			return Execute(conn => conn.Execute(sql, new { OrderId = orderId }));
		}

        public int Del(string orderId)
        {
            string sql = @"DELETE FROM [Order] WHERE OrderId = @OrderId";
            return Execute(connection => connection.Execute(sql, new { OrderId = orderId }));
        }

        public IEnumerable<OrderEntity> Select(int index, string key)
		{
			string where = "";
			if (!string.IsNullOrEmpty(key))
			{
				where = " WHERE WorkTitle LIKE '%" + key + "%' OE OrderId LIKE '%" + key + "%'";
			}
			string sql = @"SELECT * FROM [Order]" + where;
			return Execute(connection => connection.Query<OrderEntity>(sql).Skip((index - 1) * 50).Take(50));
		}

        public IEnumerable<OrderModel> Select(int page,int lim=50, string key="")
        {
            string where = "";
            if (!string.IsNullOrEmpty(key))
            {
                where = " WHERE WorkTitle LIKE '%" + key + "%' OR OrderId LIKE '%" + key + "%'";
            }
            string sql = @"SELECT [Order].*,(SELECT UserName FROM [User] Where [Order].UserId = [User].Id) AS UserName FROM [Order]" + where;
            return Execute(connection => connection.Query<OrderModel>(sql).Skip((page - 1) * 50).Take(50));
        }

        public IEnumerable<OrderEntity> FindById(string orderid)
		{
			string sql = @"SELECT * FROM [Order] WHERE OrderId=@OrderId";
			return Execute(connection => connection.Query<OrderEntity>(sql, new { OrderId = orderid }));
		}

        public IEnumerable<OrderEntity> FindByWorkId(int workId)
        {
            string sql = @"SELECT * FROM [Order] WHERE WorkId=@WorkId";
            return Execute(connection => connection.Query<OrderEntity>(sql, new { WorkId = workId }));
        }

        public IEnumerable<WorksModel> FindByUserId(int userId)
        {
            string sql = @"SELECT Works.Id,Works.Title,Works.Price,Works.CoverImg,(select COUNT(Id) from Census where WorkId=Works.Id and Type=3)as GouMaiNum FROM [Order] left join [Works] on [Order].WorkId=Works.Id  WHERE UserId=@UserId AND [Order].[State]=1";
            return Execute(connection => connection.Query<WorksModel>(sql, new { UserId = userId }));
        }

    }
}
